/*
SELECT * FROM FN_LOAD_TEST_RELEASE_LOTS_DETAILS(50)
*/
ALTER FUNCTION FN_LOAD_TEST_RELEASE_LOTS_DETAILS
(
	@RELEASE_ID INT
)
RETURNS @RESULTS TABLE( ROW_ID INT IDENTITY, TRLT_ID INT, FROM_PART_NUMBER VARCHAR(50), TO_PART_NUMBER VARCHAR(50), LOT_ID INT, LOT_NUMBER VARCHAR(50), QTY_AVAILABLE INT, QTY_PULLED INT, RELEASE_DATE DATETIME, WO_REV_NO VARCHAR(20), IS_DISABLED BIT DEFAULT 1, SHIP_LOT_NO VARCHAR(50), LOT_DATE_CODE VARCHAR(50), LOT_PART_NUMBER VARCHAR(50) )
AS
BEGIN

	DECLARE @LOT_CURSOR 			CURSOR
	DECLARE @WAFERIDS_CURSOR 		CURSOR
	DECLARE @PULLED_WAFER_IDS_TABLE	TABLE ( WAFER_ID INT )
	DECLARE @LOT_NUMBER 			VARCHAR(50)
	DECLARE @ROW_ID 				INT
	DECLARE @QTY_PULLED 			INT
	DECLARE @TOTAL_QTY_PULLED 		INT
	DECLARE @QTY_IN_TEST_PROCESS	INT
	DECLARE @PULLED_WAFER_IDS 		VARCHAR(100)
	DECLARE @CONCAT_WAFERIDS_INTO_STRING 	VARCHAR(100)
	DECLARE @TEST_PROCESSSTEP_ID 	INT
	DECLARE @IS_DISABLED			BIT

	INSERT INTO @RESULTS ( TRLT_ID, FROM_PART_NUMBER, TO_PART_NUMBER, LOT_ID, LOT_NUMBER, QTY_AVAILABLE, QTY_PULLED, RELEASE_DATE, WO_REV_NO, SHIP_LOT_NO, LOT_DATE_CODE, LOT_PART_NUMBER )
	SELECT 	TRLT_ID,
		P1.PART_NUMBER AS FROM_PART_NUMBER, 
		P2.PART_NUMBER AS TO_PART_NUMBER, 
		--LT.LOT_ID, 
		( CASE WHEN TRLT.LOT_ID = -1 THEN ( SELECT TOP 1 LOT_ID FROM LOT_TABLE LT, PROCESS_STEP PS WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_NUMBER = TRLT.LOT_NUMBER AND PS_TYPE='ASSEMBLY' ) ELSE TRLT.LOT_ID END ), 
		ISNULL( TRLT.LOT_NUMBER, (SELECT TOP 1 LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = TRLT.LOT_ID ) ), 
		QTY_AVAILABLE, 
		QTY_PULLED, 
		RELEASE_DATE, 
		WO_REV_NO,ISNULL(SHIPPED_LOT_NUMBER,ISNULL( TRLT.LOT_NUMBER, (SELECT TOP 1 LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = TRLT.LOT_ID ) ) ),
		(SELECT LOT_DATE_CODE FROM LOT_TABLE WHERE LOT_ID = TRLT.LOT_ID),
		(SELECT P.PART_NUMBER FROM PRODUCT P, LOT_TABLE LT WHERE P.ProcessStep_ID = LT.ProcessStep_ID AND LT.LOT_ID = TRLT.LOT_ID)
	FROM TEST_RELEASE_LOT_TABLE TRLT, TEST_RELEASE_TABLE TRT, PRODUCT P1, PRODUCT P2 WHERE TRLT.TEST_REL_ID = TRT.TEST_REL_ID AND TRT.FROM_PROCESSSTEP_ID = P1.PROCESSSTEP_ID AND TRT.TO_PROCESSSTEP_ID = P2.PROCESSSTEP_ID AND TRLT.TEST_REL_ID = @RELEASE_ID 

	--FROM TEST_RELEASE_LOT_TABLE TRLT, TEST_RELEASE_TABLE TRT, PRODUCT P1, PRODUCT P2, LOT_TABLE LT WHERE TRLT.TEST_REL_ID = TRT.TEST_REL_ID AND TRT.FROM_PROCESSSTEP_ID = P1.PROCESSSTEP_ID AND TRT.TO_PROCESSSTEP_ID = P2.PROCESSSTEP_ID AND TRLT.LOT_ID = LT.LOT_ID AND TRLT.TEST_REL_ID = @RELEASE_ID 
		
	/*SELECT @TEST_PROCESSSTEP_ID = TO_PROCESSSTEP_ID FROM TEST_RELEASE_TABLE WHERE TEST_REL_ID = @RELEASE_ID
	SET @CONCAT_WAFERIDS_INTO_STRING = ''
	SET @IS_DISABLED = 1

	SET @LOT_CURSOR = CURSOR FOR SELECT ROW_ID, LOT_NUMBER, QTY_PULLED  FROM @RESULTS WHERE QTY_AVAILABLE > 0
	OPEN @LOT_CURSOR
	FETCH NEXT FROM @LOT_CURSOR INTO @ROW_ID, @LOT_NUMBER, @QTY_PULLED
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @QTY_IN_TEST_PROCESS = ISNULL( SUM( WAFER_QTY_IN ), 0 )  FROM LOT_TABLE WHERE PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID AND FAB_LOT_NUMBER =  @LOT_NUMBER
		SELECT @TOTAL_QTY_PULLED = ISNULL( SUM( QTY_PULLED ), 0 ) FROM BUMP_RELEASE_LOT_TABLE BRLT, BUMP_RELEASE_TABLE BRT WHERE BRLT.BUMP_REL_ID = BRT.BUMP_REL_ID AND BUMP_REL_STATUS <> 'CANCELLED' AND BRLT.LOT_NUMBER = @LOT_NUMBER

		IF( @QTY_IN_TEST_PROCESS > 0 )
		BEGIN
			SET @IS_DISABLED = 0	
		END

		UPDATE @RESULTS SET IS_DISABLED = @IS_DISABLED WHERE ROW_ID = @ROW_ID 
		
		SET @IS_DISABLED = 1

		FETCH NEXT FROM @LOT_CURSOR INTO @ROW_ID, @LOT_NUMBER, @QTY_PULLED
	END
	CLOSE @LOT_CURSOR
	DEALLOCATE @LOT_CURSOR */

	RETURN
END

